#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import mysql.connector
import sys

# 数据库配置
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'oa5',
    'charset': 'utf8mb4'
}

def check_field_exists(cursor, table, field):
    """检查字段是否存在"""
    cursor.execute(f"SHOW COLUMNS FROM {table} LIKE '{field}'")
    return cursor.fetchone() is not None

def add_column_if_not_exists(cursor, table, field_def):
    """添加字段（如果不存在）"""
    sql = f"ALTER TABLE {table} ADD COLUMN {field_def}"
    try:
        cursor.execute(sql)
        print(f"✅ 成功添加字段: {table}.{field_def.split()[0]}")
        return True
    except mysql.connector.Error as e:
        if e.errno == 1060:  # Duplicate column name
            print(f"⚠️  字段已存在，跳过: {table}.{field_def.split()[0]}")
            return False
        else:
            print(f"❌ 添加字段失败: {e}")
            return False

def create_index_if_not_exists(cursor, index_name, table, columns):
    """创建索引（如果不存在）"""
    sql = f"CREATE INDEX {index_name} ON {table} ({columns})"
    try:
        cursor.execute(sql)
        print(f"✅ 成功创建索引: {index_name}")
        return True
    except mysql.connector.Error as e:
        if e.errno == 1061:  # Duplicate key name
            print(f"⚠️  索引已存在，跳过: {index_name}")
            return False
        else:
            print(f"❌ 创建索引失败: {e}")
            return False

def main():
    try:
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        
        print("=" * 60)
        print("开始执行营销CRM数据库优化...")
        print("=" * 60)
        
        # 1. 客户表优化
        print("\n1. 客户表优化 (oa_crm_customer)")
        print("-" * 40)
        customer_fields = [
            "last_followup_time BIGINT(11) DEFAULT 0 COMMENT '最后跟进时间'",
            "followup_count INT(11) DEFAULT 0 COMMENT '累计跟进次数'",
            "opportunity_count INT(11) DEFAULT 0 COMMENT '累计销售机会数'",
            "order_count INT(11) DEFAULT 0 COMMENT '累计订单数'",
            "total_order_amount DECIMAL(15,2) DEFAULT 0.00 COMMENT '累计订单金额'",
            "last_order_time BIGINT(11) DEFAULT 0 COMMENT '最后订单时间'",
            "customer_tags VARCHAR(500) DEFAULT '' COMMENT '客户标签（JSON格式）'",
            "important_level TINYINT(1) DEFAULT 1 COMMENT '重要程度：1-普通 2-重要 3-关键'",
            "next_followup_plan TEXT COMMENT '下一步跟进计划'",
            "lost_reason VARCHAR(255) DEFAULT '' COMMENT '客户流失原因'"
        ]
        
        for field in customer_fields:
            add_column_if_not_exists(cursor, 'oa_crm_customer', field)
        
        # 客户表索引
        customer_indexes = [
            ("idx_customer_last_followup", "last_followup_time"),
            ("idx_customer_user_id", "user_id"),
            ("idx_customer_status", "status"),
            ("idx_customer_is_cooperation", "is_cooperation"),
            ("idx_customer_important_level", "important_level")
        ]
        
        for idx_name, columns in customer_indexes:
            create_index_if_not_exists(cursor, idx_name, 'oa_crm_customer', columns)
        
        # 2. 销售机会表优化
        print("\n2. 销售机会表优化 (oa_customer_chance)")
        print("-" * 40)
        
        # 字段重命名
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN cid customer_id INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '客户ID'")
            print("✅ cid -> customer_id")
        except:
            print("⚠️  cid重命名失败或已存在")
            
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN contact_id primary_contact_id INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '主要联系人ID'")
            print("✅ contact_id -> primary_contact_id")
        except:
            print("⚠️  contact_id重命名失败或已存在")
            
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN services_id service_type_id INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务类型ID'")
            print("✅ services_id -> service_type_id")
        except:
            print("⚠️  services_id重命名失败或已存在")
            
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN discovery_time discovery_date BIGINT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '发现日期'")
            print("✅ discovery_time -> discovery_date")
        except:
            print("⚠️  discovery_time重命名失败或已存在")
            
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN expected_time expected_sign_date BIGINT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '预计签单日期'")
            print("✅ expected_time -> expected_sign_date")
        except:
            print("⚠️  expected_time重命名失败或已存在")
            
        try:
            cursor.execute("ALTER TABLE oa_customer_chance CHANGE COLUMN expected_amount expected_order_amount DECIMAL(15,2) DEFAULT 0.00 COMMENT '预计订单金额'")
            print("✅ expected_amount -> expected_order_amount")
        except:
            print("⚠️  expected_amount重命名失败或已存在")
        
        chance_fields = [
            "opportunity_code VARCHAR(50) DEFAULT '' COMMENT '机会编号'",
            "category TINYINT(4) DEFAULT 1 COMMENT '机会类别：1-案件委托 2-法务需求 3-法务专项'",
            "level TINYINT(4) DEFAULT 3 COMMENT '机会等级：1-A级（本月可签）2-B级（30天可签）3-C级（持续跟进）'",
            "source VARCHAR(50) DEFAULT '' COMMENT '机会来源：客户推荐、广告、主动咨询等'",
            "status TINYINT(4) DEFAULT 1 COMMENT '状态：1-有效 2-已转化 3-已失效'",
            "convert_order_id INT(11) DEFAULT 0 COMMENT '转化后的订单ID'",
            "convert_time BIGINT(11) DEFAULT 0 COMMENT '转化时间'",
            "lose_reason VARCHAR(255) DEFAULT '' COMMENT '失效原因'",
            "lose_time BIGINT(11) DEFAULT 0 COMMENT '失效时间'",
            "user_name VARCHAR(50) DEFAULT '' COMMENT '负责人姓名（冗余）'",
            "department_id INT(11) DEFAULT 0 COMMENT '部门ID'",
            "remarks TEXT COMMENT '备注'"
        ]
        
        for field in chance_fields:
            add_column_if_not_exists(cursor, 'oa_customer_chance', field)
        
        # 销售机会表索引
        chance_indexes = [
            ("idx_chance_customer_id", "customer_id"),
            ("idx_chance_status", "status"),
            ("idx_chance_level", "level"),
            ("idx_chance_expected_sign_date", "expected_sign_date"),
            ("idx_chance_user_id", "belong_uid"),
            ("idx_chance_category", "category")
        ]
        
        for idx_name, columns in chance_indexes:
            create_index_if_not_exists(cursor, idx_name, 'oa_customer_chance', columns)
        
        conn.commit()
        print("\n" + "=" * 60)
        print("✅ 客户表和销售机会表优化完成！")
        print("=" * 60)
        
        cursor.close()
        conn.close()
        
    except mysql.connector.Error as e:
        print(f"❌ 数据库错误: {e}")
        sys.exit(1)

if __name__ == '__main__':
    main()
